

---------------------------------------------------------------------------
-- BEGIN MAINTENANCE WRAPPER
----------------------------
USE master;
-- Replace SQL_Server_logon with your SQL Server user credentials.
GRANT EXTERNAL ACCESS ASSEMBLY TO [NT AUTHORITY\SYSTEM]; 
PRINT '<<< GRANT EXTERNAL ACCESS ASSEMBLY TO [NT AUTHORITY\SYSTEM] - Completed with SUCCESS >>>'

-- Modify the following line to specify a different database.
ALTER DATABASE Lib SET TRUSTWORTHY ON;
PRINT '<<< ALTER DATABASE Lib SET TRUSTWORTHY ON - Completed with SUCCESS >>>'

USE Lib; 
--Enable CLR Integration if is not enabled
IF NOT EXISTS (SELECT * FROM sys.configurations WHERE [name] = 'show advanced options' AND [value] = 1) BEGIN 
	EXEC sp_configure 'show advanced options', 1;
	RECONFIGURE;
	PRINT '<<< Enable SQL Advanced Options - Completed with SUCCESS >>>'
END

IF NOT EXISTS (SELECT * FROM sys.configurations WHERE [name] = 'clr enabled' AND [value] = 1) BEGIN 
	EXEC sp_configure 'clr enabled', 1
	RECONFIGURE
	PRINT '<<< Enable CLR - Completed with SUCCESS >>>'
END

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'clr' AND ROUTINE_NAME = 'fn_win_eventlog' AND ROUTINE_TYPE = 'FUNCTION') BEGIN
	DROP FUNCTION [clr].[fn_win_eventlog]
	PRINT '<<< DROP FUNCTION [clr].[fn_win_eventlog] - Completed with SUCCESS >>>'
END


--Drop the assembly if it exist
IF EXISTS (SELECT * FROM sys.assemblies asms WHERE asms.name = N'ClrWinTools') BEGIN 
	DROP ASSEMBLY [ClrWinTools]
	PRINT '<<< DROP Assembly ClrWinTools - Completed with SUCCESS >>>'
END

-- Create the assembly if it does not exists
CREATE ASSEMBLY ClrWinTools 
FROM 'D:\Projects\SqlLib\Project\CLR\WinTools\bin\Debug\ClrWinTools.dll' 
WITH PERMISSION_SET = EXTERNAL_ACCESS;
GO	
PRINT '<<< CREATE Assembly ClrWinTools - Completed with SUCCESS >>>'


GO
----------------------------
-- END MAINTENANCE WRAPPER
---------------------------------------------------------------------------


SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


/*================================================================================================
Name:			[clr].[win_eventlog]
Type:			Custom CLR UDF
Owner:					
Description:	Allows access to Windows EventLog from TSQL
Parameters:		@logname   
Dependens on:  	ClrWinTools.dll
Usage:			SELECT * FROM clr.win_eventlog('Application')	
--------------------------------------------------------------------------------------------------
Project:		http://sqllib.codeplex.com/
Help:			http://sqllib.codeplex.com/wikipage?title=win_eventlog&referringTitle=Documentation		
Version:		20121101
SrcCtrlApp:		SVN
SrcCtrlPath:	http://sqllib.codeplex.com/SourceControl/list/changesets
--------------------------------------------------------------------------------------------------
History:		11/01/2012 - Adrian E Dudau - Function created
================================================================================================*/
CREATE FUNCTION clr.fn_win_eventlog(@logname nvarchar(255))
RETURNS TABLE 
	--(LogName NVARCHAR(255)
	--,[Source] NVARCHAR(255)
	--,[EventID] BIGINT 
	--,[Level] NVARCHAR(255)
	--,[User] NVARCHAR(255)
	--,OpCode BIGINT
	--,Logged DATETIME
	--,TaskCategory NVARCHAR(255)
	--,Keywords NVARCHAR(4000)
	--,Computer NVARCHAR(255)
	--,MoreInformation NVARCHAR(255)
	--,[Message] NVARCHAR(MAX)
	--)
(Logged DATETIME, [Message] NVARCHAR(MAX), TaskCategory NVARCHAR(255),[EventID] BIGINT,[Level] NVARCHAR(255),[User] NVARCHAR(255),Computer NVARCHAR(255),[Source] NVARCHAR(255))	
	
AS 
EXTERNAL NAME ClrWinTools.ClrEventLog.GetEventLog;
GO



---------------------------------------------------------------------------
-- BEGIN MAINTENANCE WRAPPER
----------------------------
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'clr' AND ROUTINE_NAME = 'fn_object_filegroup' AND ROUTINE_TYPE = 'FUNCTION') BEGIN
	PRINT '<<< CREATE FUNCTION [clr].[fn_object_filegroup] - Completed with SUCCESS >>>'
END

ELSE
	PRINT '<<< CREATE FUNCTION [clr].[fn_object_filegroup] - Completed with ERROR >>>'

----------------------------
-- END MAINTENANCE WRAPPER
---------------------------------------------------------------------------

